<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars
define("PRIMARY_KEY","nobkt");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_closing_jurpost.php";
$FileTemplate  = "ais_closing_jurpost_list.html";
$TableName     = "ais_advance";
$SubHeaderTitle= "Closing - Journal Posting";
$PageSize      = 10;
$HTMLGridList  = "ClosingPeriod";

//************ FORM ACTION **************


if (GetParam("Action","")=="Posting") {
   if (CheckLedger(GetParam("periodYear",""),GetParam("periodMonth",""))) {
     GetMemorialRupiah(GetParam("periodYear",""),GetParam("periodMonth",""));
   } else

   print "
              <script>
              alert('Period Allready Closed!\\nNo progress...');
              </script>
   ";
}

function CheckLedger($Year, $Month) {
   global $DBConnection;

   //dyah.110809
   //if ($Month=="AUDIT") $Month="12";
    if ($Month=="AUDIT"){
        if (strlen($Year)>2) $Year13 = substr($Year,-2);
        $SQL = "SELECT status FROM ais_history_ledger WHERE ledger='".$Year13."13' ";
        $result = $DBConnection->dbc->get_var($SQL);
    }else{
       $SQL = "SELECT status FROM ais_history_ledger WHERE ledger='".BuildLedger($Year,$Month)."' ";
       $result = $DBConnection->dbc->get_var($SQL);
       //print $SQL;die();
    }
   if ($result=='Open')  return true;
   else return false;

}

function GetMemorialRupiah($Year, $Month) {
  global $DBConnection;
  //Clearing COA mutd,mutk,aku
  if ($Month<>"AUDIT") {
      if ($Month*1<10) $bulan = "0".$Month;
      else $bulan = $Month;
      if ($Month*1-1<10) $lastbl = "0".($Month*1-1);
      else $lastbl = ($Month*1-1);

      if ($Month*1+1<10) $nextbl = "0".($Month*1+1);
      else $nextbl = ($Month*1+1);
  }
  //print $nextbl;die();
  if ($Month=="AUDIT")
  $SQL = "UPDATE tblcoa SET mutk13=0, mutd13=0, aku13=0 WHERE ledger='$Year' AND ktrasl='".GetOfficeID()."'";
  else
  $SQL = "UPDATE tblcoa SET mutk$bulan=0, mutd$bulan=0, aku$bulan=0 WHERE ledger='$Year' AND ktrasl='".GetOfficeID()."'";
  //print $SQL;die();
  $DBConnection->dbc->query($SQL);


  $SQL = "DROP TABLE IF EXISTS ais_memorial_tocoa";
  $DBConnection->dbc->query($SQL);

  //$TEMPORARY = "TEMPORARY";
  $SQL = "
    CREATE $TEMPORARY TABLE `ais_memorial_tocoa` (
    `skada` VARCHAR( 64 ) NOT NULL ,
    `debet` DECIMAL( 15, 4 ) NOT NULL ,
    `kredit` DECIMAL( 15, 4 ) NOT NULL
    )
  ";

  $DBConnection->dbc->query($SQL);
/*
  $SQL = "
    INSERT INTO `ais_memorial_tocoa`
    SELECT skada, SUM(IF(dk='D',rupiah,0)), SUM(IF(dk='K',rupiah,0))
    FROM ais_memorial
    WHERE (nobkt not like 'MC%' OR nobkt not like 'ME%') AND ktrasl='".GetOfficeID()."' AND month(tglbkt)=$Month AND year(tglbkt)=$Year
    GROUP BY SKADA
  ";
*/
if ($Month<>"AUDIT") {
  $SQL = "
    INSERT INTO `ais_memorial_tocoa`
    SELECT skada, SUM(IF(dk='D',rupiah,0)), SUM(IF(dk='K',rupiah,0))
    FROM ais_memorial
    WHERE ktrasl='".GetOfficeID()."' AND month(tglbkt)=$Month AND year(tglbkt)=$Year AND (nobkt not like 'MA%')
    GROUP BY SKADA
  ";
} else {

  //dyah.110809
  /*$SQL = "
    INSERT INTO `ais_memorial_tocoa`
    SELECT skada, SUM(IF(dk='D',rupiah,0)), SUM(IF(dk='K',rupiah,0))
    FROM ais_memorial
    WHERE ktrasl='".GetOfficeID()."' AND month(tglbkt)=12 AND year(tglbkt)=$Year AND (nobkt like 'MA%')
    GROUP BY SKADA
  ";*/
  $SQL = "
    INSERT INTO `ais_memorial_tocoa`
    SELECT skada, SUM(IF(dk='D',rupiah,0)), SUM(IF(dk='K',rupiah,0))
    FROM ais_memorial
    WHERE ktrasl='".GetOfficeID()."' AND month(tglbkt)=12 AND year(tglbkt)=$Year AND ((nobkt like 'MA%') OR (nobkt REGEXP '^MM[0-9]{2}13'))
    GROUP BY SKADA
  ";
  $bulan=13;
}
  $DBConnection->dbc->query($SQL);

  $SQL = "SELECT * FROM ais_memorial_tocoa";
  $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
  if ($MyResult) {
    foreach ($MyResult as $RowResult) {
      $SQL = "UPDATE tblcoa SET mutk$bulan='".$RowResult["kredit"]."', mutd$bulan='".$RowResult["debet"]."'
              WHERE skada='".$RowResult["skada"]."' AND ledger='$Year' AND ktrasl='".GetOfficeID()."' ";
      $DBConnection->dbc->query($SQL);
    }
  }

  //UPDATE REVENUE

   $SQL = "SELECT sum(mutd$bulan) as debet,sum(mutk$bulan)  as kredit
           FROM tblcoa
           LEFT JOIN tblcoagroup ON tblcoa.grup=tblcoagroup.group_id
           WHERE (coatype = 'REVENUES' OR coatype = 'EXPENSES' OR coatype = 'OTHER EXPENSES') AND ledger='$Year'
           ";
  $MyResult = $DBConnection->dbc->get_row($SQL);
  $SQL  = "UPDATE tblcoa SET mutd$bulan='".$MyResult->debet."', mutk$bulan='".$MyResult->kredit."' WHERE skada='".Get_salrlytd()."' AND ledger='".GetParam("periodYear","")."'";
  $DBConnection->dbc->query($SQL);
  //$DBConnection->dbc->debug();

  $ledger_year = substr($Year,-2);

  if ($Month==1) {
    $SQL = "UPDATE tblcoa SET aku$bulan=awal+mutd$bulan-mutk$bulan";
    $DBConnection->dbc->query($SQL);
  } elseif ($Month=="AUDIT") {
    $SQL = "UPDATE tblcoa SET aku13=aku12+mutd13-mutk13";
    $DBConnection->dbc->query($SQL);
  } else {
    $SQL = "UPDATE tblcoa SET aku$bulan=aku$lastbl+mutd$bulan-mutk$bulan";
    $DBConnection->dbc->query($SQL);
  }

  //UPDATE HISTORY LEDGER
  /*if ($Month=="AUDIT") $Month=12;
  $SQL = "UPDATE ais_history_ledger SET status='Close' WHERE ktrasl='".GetOfficeID()."' AND ledger='".BuildLedger($Year,$Month)."'";
  $DBConnection->dbc->query($SQL);

  $SQL = "SELECT ledger FROM ais_history_ledger WHERE ledger='".BuildLedger($Year,$Month+1)."'";
  $MyLedger = $DBConnection->dbc->get_var($SQL);

  if (!strlen($MyLedger)) {
    $SQL = "INSERT INTO ais_history_ledger VALUES ('".GetOfficeID()."','".ReturnPeriod($Year,$Month+1,0)."','".ReturnPeriod($Year,$Month+1,1)."','".BuildLedger($Year,$Month+1)."','Open')";
    $DBConnection->dbc->query($SQL);
  } else {
    $SQL = "UPDATE ais_history_ledger SET status = 'Open' WHERE ledger='".BuildLedger($Year,$Month+1)."'";
    $DBConnection->dbc->query($SQL);
  }*/

  //dyah.110809

  if($Month<>"AUDIT"){
      $SQL = "UPDATE ais_history_ledger SET status='Close' WHERE ktrasl='".GetOfficeID()."' AND ledger='".BuildLedger($Year,$Month)."'";
      $DBConnection->dbc->query($SQL);

      $SQL = "SELECT ledger FROM ais_history_ledger WHERE ledger='".BuildLedger($Year,$Month+1)."'";
      $MyLedger = $DBConnection->dbc->get_var($SQL);

      if (!strlen($MyLedger)) {
        $SQL = "INSERT INTO ais_history_ledger VALUES ('".GetOfficeID()."','".ReturnPeriod($Year,$Month+1,0)."','".ReturnPeriod($Year,$Month+1,1)."','".BuildLedger($Year,$Month+1)."','Open')";
        $DBConnection->dbc->query($SQL);
      } else {
        $SQL = "UPDATE ais_history_ledger SET status = 'Open' WHERE ledger='".BuildLedger($Year,$Month+1)."'";
        $DBConnection->dbc->query($SQL);
      }

      if($Month == 12){
                if (strlen($Year)>2) $Year13 = substr($Year,-2);
                $SQL = "SELECT ledger FROM ais_history_ledger WHERE ledger='".$Year13."13'";
                $MyLedger = $DBConnection->dbc->get_var($SQL);

                if (!strlen($MyLedger)) {
                    $SQL = "INSERT INTO ais_history_ledger VALUES ('".GetOfficeID()."','".$Year."','13','".$Year13."13','Open')";
                    $DBConnection->dbc->query($SQL);
              } else {
                    $SQL = "UPDATE ais_history_ledger SET status = 'Open' WHERE ledger='".$Year13."13'";
                    $DBConnection->dbc->query($SQL);
                }
      }

   }else{
        $SQL = "UPDATE ais_history_ledger SET status='Close' WHERE ktrasl='".GetOfficeID()."' AND ledger='".substr($Year,-2)."13'";
        $DBConnection->dbc->query($SQL);
   }

  print "<script>alert('Posting Success!')</script>";

}

function Get_salrlytd() {
   global $DBConnection;
  $SQL = "SELECT salrlytd FROM tblprofile";
  return $DBConnection->dbc->get_var($SQL);
}


//******* SHOW FORM CONTENT *********
$TBS = new clsTinyButStrong;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("monthformblk",GetArrayMonth());
$ArrayYear[0]["year"] = date("Y");
$ArrayYear[1]["year"] = date("Y")-1;
$TBS->MergeBlock("yearformblk",$ArrayYear);
$PageNum = 1;
if (GetParam("PageNum","")) $PageNum  = GetParam("PageNum","");
$SQLCount = "SELECT count(*) FROM ais_history_ledger ORDER BY ledger DESC";

$RecCnt = $DBConnection->dbc->get_var($SQLCount) ;
$SQL = "SELECT * FROM ais_history_ledger ORDER BY ledger DESC";
$TBS->MergeBlock("blg",$DBMysql,$SQL,$PageSize,$PageNum,$RecCnt);
$TBS->MergeNavigationBar('nv',array('size'=>10,'pos'=>'centred'),$PageNum,$RecCnt,$PageSize);
$TBS->Show();


?>
